package Server;



import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.FileReader;
import java.io.FileWriter;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.io.PrintWriter;
import java.sql.Connection;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.text.DateFormat;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.Collections;
import java.util.Date;
import java.util.HashSet;
import java.util.Set;

import javax.swing.JFrame;
import javax.swing.JOptionPane;

import Library.Books;
import Library.Books2;
import Library.History;
import Library.LateReport;
import Library.LendingHistoryStudent;
import Library.ListOrderBookStudent;
import Library.Login;
import Library.OrderBookStudent;
import Library.ReaderCard;
import Library.Report;
import Library.Search;
import Library.SendMail;
import Library.StatisticInfo;

public class DBmethods {

/**
 * 
 * update list of loans
 * @param reader that we made him loan
 * @return  list of loans
 */
	public static ArrayList<String[]> extendLoan(String reader) {
		Statement stmt;
		int readerId;
		readerId=Integer.parseInt(reader);
		ArrayList<String[]> list=new ArrayList();
		
	
		try 
		{
			stmt = EchoServer.conn.createStatement();
			ResultSet rs = stmt.executeQuery("SELECT * FROM loans;"); 
	 		while(rs.next())
	 		{
	 			
				 if(rs.getInt(2)==readerId)
				 {
					String s[]={rs.getString(1),rs.getString(3),rs.getString(4),rs.getString(5),rs.getString(6),rs.getString(7)};
					list.add(s);
				
					 }
				 }
	 		
	 		rs.close();
	 		return list;		
			
		} catch (SQLException e) {e.printStackTrace();}
		return null;
	}
	
	/**
	 * @param userName user name of user to check log in
	 * @param password password of user to check log in
	 * @return instance of log in
	 * this method checks if user can log in.if yes commit log in*/
	public static Login CheckLogIn(String userName,	String password)
	{
		Login user = new Login(userName,password);
		Statement stmt;
		try 
			{
				stmt = EchoServer.conn.createStatement();
				
				ResultSet rs = stmt.executeQuery("SELECT * FROM user WHERE userName='"+userName+"';");
		 		if(rs.next())
		 		{
					 if(rs.getString(2).equals(password))	// check password
					 {
						 if(rs.getInt(3)==0)	// check if the user is already logged 
						 {
							 changeStatus(userName,1);
							 user.setRank(rs.getInt(4));	// update user's permission
						     user.setStatus(0); // Success login
						 }
						 else  user.setStatus(1);	// case the user is already logged 
					 }
					 else user.setStatus(2); // wrong password	 
				} 
		 		else user.setStatus(3); // user does not exists 
				rs.close();
				return user;	// case user not exists  
			}
		catch (SQLException e) {e.printStackTrace();}
		return user;	// SOL error
	}
	
	/**
	 *@param user2 user name of the wanted user to change his status
	 *@param status new status
	 *@return void
	 *this method changes status of user2 to the given status*/
	public static  boolean changeStatus(String user2,int status) {
		Statement stmt;
		try 
		{
			PreparedStatement update=EchoServer.conn.prepareStatement("UPDATE USER SET STATUS=? WHERE userName=?");
			update.setInt(1, status);
			update.setString(2, user2);
			update.execute();
			
		} catch (SQLException e)
		{
			e.printStackTrace();
			return false;
		}
		return true;
		
	}
	
	/**
	 *@param user user name of currently connected user to db
	 *this method checks if there are any more connected users to server beside current user
	 *@return true if there are more connected users, else false*/
	public static boolean isAnyConnected(String user)//checks if there is any user connected to db beside current user
	{
		Statement stmt;
		try 
			{
				stmt = EchoServer.conn.createStatement();
				ResultSet rs = stmt.executeQuery("SELECT * FROM user;");
		 		while(rs.next())
		 		{
		 			if((rs.getInt(3)==1)&&(rs.getString(1)!=user))
		 				return true;
		 		}
			}
		catch (SQLException e) {e.printStackTrace();}
		return false;
	}
	
	/**
	 *@param cId copy id of checked copy
	 *@return true if copy is loaned, else false*/
	public static boolean isLoaned(int cId)
	{
		Statement stmt;
		try 
			{
				stmt = EchoServer.conn.createStatement();
				ResultSet rs = stmt.executeQuery("SELECT * FROM copies WHERE copyId="+cId+";");
				if(rs.next())
				{
					if(rs.getInt(3)==0) return true;
				}
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		return false;
	}

	/**
	 * create new reader card
	 * @param userName
	 * @param phone
	 * @param FirstName
	 * @param LastName
	 * @param email
	 * @return the result of the insetin to the DB
	 * @throws SQLException
	 */
	public static String newReaderCard(String userName,
			String phone, String FirstName, String LastName,String email) throws SQLException {
		int rId=getNextReaderId();
		PreparedStatement update;
		try {
		update = EchoServer.conn.prepareStatement("INSERT INTO readercard VALUES(?,?,?,?,?,?,'active');");
		String res=checkTheUserName(userName);
		if(res.equals("user exist already"))
			return "user exist already";
		if(res.equals("there is no such user"))
			return "There is no such user in the system";
		
		update.setInt(1, rId);
		update.setString(2, userName);
		update.setString(3,phone);
		update.setString(4, email);
		update.setString(5,FirstName);
		update.setString(6,LastName);
		update.execute();
		
		return("reader card Added successfully");
		
		
		
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return null;
		
		
	}
/**
 * Checking if the user name of the new reader is avaialbe
 * @param userName
 * @return 
 */
	private static String checkTheUserName(String userName) {
	
		Statement stmt;
		try {
		stmt = EchoServer.conn.createStatement();
		ResultSet rs = stmt.executeQuery("SELECT * FROM readercard;");
		while(rs.next())
		{
			if(rs.getString(2).equals(userName))
				return ("user exist already");
		}
		rs = stmt.executeQuery("SELECT * FROM user;");
		while(rs.next())
		{
			if(rs.getString(1).equals(userName))
				return ("ok");
		}
		return ("there is no such user");
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return userName;
		
	}
	
	/**
	 * next reader id that aviable
	 * @return the next reader id that aviable
	 * @throws SQLException
	 */
	private static int getNextReaderId() throws SQLException {
		Statement stmt;
		stmt = EchoServer.conn.createStatement();
		ResultSet rs = stmt.executeQuery("SELECT * FROM readercard;");
		rs.last();
		return ((rs.getInt(1)+1));
	}

	/**
	 *@return next report id*/
	public static int getNextReportId()
	{
		Statement stmt;
		try {
			stmt = EchoServer.conn.createStatement();
			ResultSet rs = stmt.executeQuery("SELECT * FROM reports;");
			if(rs.last())
				return ((rs.getInt(1)+1));
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return 1;
	}
	
	/**
	 * change the status of the reader card
	 * @param rId reader id that we changing his staus
	 * @param status
	 * @return
	 */
	public static boolean changeReaderCardStatus(int rId,String status)
	{
		try 
		{
			PreparedStatement update=EchoServer.conn.prepareStatement("UPDATE readercard SET STATUS=? WHERE readerId=?");
			update.setString(1, status);
			update.setInt(2, rId);
			update.execute();
			return true;
		} catch (SQLException e) {e.printStackTrace();}
		return false;
	}
	
	/**
	 * checking if the reader have books in loand
	 * @param rId
	 * @return 1 if he has
	 */
	public static int checkForLoanedBooks(String rId)
	{
		Statement stmt;
		try {	
			stmt = EchoServer.conn.createStatement();
			ResultSet rs = stmt.executeQuery("SELECT * FROM loans;");
	 		while(rs.next())
	 		{
				 if(rs.getString(2).equals(rId))
				 {
					return 1;
				 }
			} 
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return 0;
	}

	
	/**
	 * @param id book id to check if exists in db
	 * @return true if book id number id is already exists in db,else false*/
	public static boolean bookExists(int id)
	{
		Statement stmt;
		try {	
			stmt = EchoServer.conn.createStatement();
			ResultSet rs = stmt.executeQuery("SELECT * FROM books;");
	 		while(rs.next())
	 		{
				 if(rs.getInt(1)==id)
					 return true;
			} 
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return false;
	}
	/**
	 * return list of the books
	 * @return
	 */
	public static ArrayList<String[]> getBooks()
	{
		ArrayList<String[]> books=new ArrayList<String[]>();
		Statement stmt;
		try {	
			stmt = EchoServer.conn.createStatement();
			ResultSet rs = stmt.executeQuery("SELECT * FROM books;");
	 		while(rs.next())
	 		{
	 			String s[]={rs.getString(1),rs.getString(2),rs.getString(8)};
				books.add(s);
			} 
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return books;
	}
	
	/**
	 * @param bk2 instance of book to add to db
	 * @return true in case adding book bk2 to db succeeded, else false*/
	public static boolean addBook(Books2 bk2)
	{
		int len;
		try {
			PreparedStatement update=EchoServer.conn.prepareStatement("INSERT INTO books VALUES(?,?,?,?,?,?,?,?,?,?,?,?,?);");
			update.setInt(1,bk2.getBookId());
			update.setString(2,bk2.getBookName());
			update.setString(3,bk2.getAuthor());
			update.setInt(4,bk2.getEdition());
			update.setString(5,bk2.getDatePrinted());
			update.setString(6,bk2.getScope());
			update.setString(7,bk2.getDescription());
			update.setInt(8,bk2.getCatalogNum());
			update.setInt(9,bk2.getNumCopies());
			update.setString(10,bk2.getPurchaseDate());
			update.setInt(11,bk2.getShelfLocation());
			update.setInt(12,bk2.getRank());
			FileOutputStream fos;
			try {
				fos = new FileOutputStream("c:/files/"+bk2.getBookName()+bk2.getBookId()+".pdf");
				fos.write(bk2.getb(),0,bk2.getb().length);
			} catch (IOException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
			update.setString(13,"c:/files/"+bk2.getBookName()+bk2.getBookId()+".pdf");
			update.execute();
			return true;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return false;
	}
	
	/**
	 * @param id book id to remove from db
	 * @return if succeeded or not in removing book/can't remove because there are
	 *  loaned copies of this book
	 * */
	public static String deleteBook(int id)
	{
		Statement stmt;
		String s;
		try {
			stmt = EchoServer.conn.createStatement();
			ResultSet rs=stmt.executeQuery("SELECT * FROM copies;");
			while(rs.next())
			{
				if(rs.getInt(2)==id)
				{
					/*if(rs.getInt(3)==0)
					{
						s="Book number "+id+" can not be removed from catalog because there are loaned copies of this book";
						return s;
					}*/
					s="Book number "+id+" can not be removed before removing all of it's copies from catalog";
					return s;
				}
			}
			PreparedStatement update=EchoServer.conn.prepareStatement("DELETE FROM books WHERE BookId=?;");
			update.setInt(1,id);
			update.execute();
			s="Book number "+id+" was removed successfully from catalog";
			return s;
			
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		s="Book number "+id+" was not removed successfully from catalog\nTry again";
		return s;
	}
	
	/**
	 * @param cId copy id of wanted copy to match it's book
	 * @return book id that cId is copy of it*/
	public static int matchCidToBid(int cId)
	{
		Statement stmt;
		try
		{
			stmt = EchoServer.conn.createStatement();
			ResultSet rs=stmt.executeQuery("SELECT * FROM copies;");
			while(rs.next())
			{
				if(rs.getInt(1)==cId)
				{
					return rs.getInt(2);
				}
			}
		}
		catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return -1;
	}
	
	/**
	 * @param cId copy id to check if exists in db
	 * @return true if copy id number cId is already exists in db,else false*/
	public static boolean copyExists(int cId)
	{
		Statement stmt;
		int bId=matchCidToBid(cId);
		try {	
			stmt = EchoServer.conn.createStatement();
			ResultSet rs = stmt.executeQuery("SELECT * FROM copies;");
	 		while(rs.next())
	 		{
				 if((rs.getInt(1)==cId)&&(rs.getInt(2)==bId))
					 return true;
			} 
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return false;
	}
	
	/**
	 * @return ArrayList<String[]> of copies in db*/
	public static ArrayList<String[]> getCopies()
	{
		ArrayList<String[]> copies=new ArrayList<String[]>();
		Statement stmt;
		try {	
			stmt = EchoServer.conn.createStatement();
			ResultSet rs = stmt.executeQuery("SELECT * FROM copies ORDER BY bookId;");
	 		while(rs.next())
	 		{
	 			String s[]={rs.getString(1),rs.getString(2),rs.getString(3)};
				copies.add(s);
			} 
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return copies;
	}
	
	/**
	 * @param cId copy id of new copy to add to db
	 * @param bId book id that cId is copy of it
	 * @return void*/
	public static void addCopy(int cId,int bId)
	{
		Statement stmt;
		try {
			//add new copy
			PreparedStatement update=EchoServer.conn.prepareStatement("INSERT INTO copies VALUES(?,?,?);");
			update.setInt(1,cId);
			update.setInt(2,bId);
			update.setInt(3,1);
			update.execute();
			stmt = EchoServer.conn.createStatement();
			ResultSet rs = stmt.executeQuery("SELECT * FROM books;");
	 		while(rs.next())
	 		{
				 if(rs.getInt(1)==bId)
				 {
					 int numOfC=rs.getInt(9);//increase number of copies of book number bId
					 numOfC++;
					 update=EchoServer.conn.prepareStatement("UPDATE books SET numCopies=? WHERE bookId=?;"); 
					 update.setInt(1,numOfC);
					 update.setInt(2,bId);
					 update.execute();
					 break;
				 } 
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}
	
	/**
	 * @param cId copy id to remove from db
	 * @param bId book id to remove it's copy from db
	 * @return void*/
	public static void deleteCopy(int cId,int bId)
	{
		Statement stmt;
		PreparedStatement update;
		try {
			stmt = EchoServer.conn.createStatement();
			ResultSet rs = stmt.executeQuery("SELECT * FROM books;");
	 		while(rs.next())
	 		{
				 if(rs.getInt(1)==bId)
				 {
					 int numOfC=rs.getInt(9);//reduce number of copies of book number bId
					 numOfC--;
					 update=EchoServer.conn.prepareStatement("UPDATE books SET numCopies=? WHERE bookId=?;"); 
					 update.setInt(1,numOfC);
					 update.setInt(2,bId);
					 update.execute();
				 } 
			}
	 		update=EchoServer.conn.prepareStatement("DELETE FROM copies WHERE copyId=?;");
			update.setInt(1,cId);
			update.execute();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}
	
	/**
	 * create new loan
	 * @param reader 
	 * @param copy
	 * @param from who
	 * @return for how much time the book had loaned
	 */
	public static String newLoan(String reader, String copy,String from) {
		PreparedStatement update;
		
		
		String check=checkActivity(reader);
		if(check.equals("active"))
		{
		try {
		if(from.equals("regular"))
				changeAviability(copy);
		update = EchoServer.conn.prepareStatement("INSERT INTO loans VALUES(?,?,?,?,?,?,?);");
		int lId=getNextLoanId();
		update.setInt(1, lId);
		update.setString(2, reader);
		
	    DateFormat dateFormat = new SimpleDateFormat("yyyy/MM/dd");
	    Calendar cal = Calendar.getInstance();
	    String  today = dateFormat.format(cal.getTime());
	    
	
		int cId=Integer.parseInt(copy);
		int rank=getPopularity(cId);
		if(rank==1)
		cal.add(Calendar.DATE, 3);
		else cal.add(Calendar.DATE, 14);
		String returnDate=dateFormat.format(cal.getTime());
		
	    update.setString(3,today);
		update.setString(4,returnDate);
		update.setInt(5, Integer.parseInt(copy));
		update.setString(6,"");
		update.setString(7,"");
		update.execute();
		changeInOrder(copy);
		if(rank==1)
			return("book loaned for 3 days");
		else return ("book loaned for 14 days");
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		
		}
		return(check);
	
		
	}

	/**
	 * change status of oreder
	 * @param copy
	 */
	private static void changeInOrder(String copy) {
		PreparedStatement update;
		try {
		update = EchoServer.conn.prepareStatement("UPDATE orders SET status=? WHERE copyId=? ");
	    update.setString(1,"not active");
	    update.setString(2,copy);
		update.execute();
	    
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		
		}

		
	/**
	 * change aviability of the copy
	 * @param copy
	 */
	private static void changeAviability(String copy) {
		
		int cId=Integer.parseInt(copy);
	    PreparedStatement update;
	    Statement stmt;
	    try {
	    stmt = EchoServer.conn.createStatement();
	    ResultSet rs=stmt.executeQuery("SELECT availability FROM copies where copyId='"+cId+"';");
	    if (rs.next())
	    {
	    if(rs.getInt(1)==1)
		update = EchoServer.conn.prepareStatement("UPDATE copies SET availability=0 WHERE copyId=? ");
	    else update = EchoServer.conn.prepareStatement("UPDATE copies SET availability=1 WHERE copyId=? ");
	    
	    update.setInt(1,cId);
		update.execute();
	    }
	    } catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}
	
	/**
	 * @param reader reader card id
	 * @return status of reader card*/
	private static String checkActivity(String reader) {

		Statement stmt;
		
			int rid=Integer.parseInt(reader);
			try {
				stmt = EchoServer.conn.createStatement();
				;
			ResultSet rs=stmt.executeQuery("SELECT status FROM readercard where readerId='"+rid+"';");
			if(rs.next())
			{
			String check=rs.getString(1);
			if (check.equals("active"))
				return ("active");
			else return ("Reader is not active");}
			
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
			return ("There is no such reader Id");
		}
	/**
	 * get next loan id that aviable
	 * @return this loan id
	 * @throws SQLException
	 */
	private static int getNextLoanId() throws SQLException {
		Statement stmt;
		Statement stmt2;
		
		stmt = EchoServer.conn.createStatement();
		stmt2 = EchoServer.conn.createStatement();
		ResultSet rs = stmt.executeQuery("SELECT loanId FROM loans ORDER BY loanId;");
		ResultSet rs2 = stmt2.executeQuery("SELECT lhId FROM loanshistory ORDER BY lhId;");
		if(!(rs.last()))
		{
			if(!rs2.last())
				return (1);
			else return (rs2.getInt(1)+1);
			
		}	
		else {
			if(!(rs2.last()))
			{
				if ((rs.getInt(1))>(rs2.getInt(1)))
						return ((rs.getInt(1)+1));
				else return((rs2.getInt(1)+1));
			}
			else return ((rs.getInt(1)+1));
		}
	
	}
	
	/**
	 * get copies that in loan
	 * @return this list
	 */
	public static ArrayList<String[]>  getCopiesInLoan()
	{
		
		Statement stmt;
		ArrayList<String[]> copies=new ArrayList<String[]>();
		try {
			stmt = EchoServer.conn.createStatement();
		
		ResultSet rs = stmt.executeQuery("SELECT copyId FROM loans;");
		while(rs.next())
 		{
			
			String str=Integer.toString(rs.getInt(1));
 			String s[]={str};
			copies.add(s);
		} 
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return copies;
		
	}
	
	/**
	 * This method finds Find the books by text and searchType
	 * @param text
	 * @param searchType:0-name book 
	 *                   1-Scope
	 *                   2-free word
	 * @return the  Search 
	 */
	public static Search SearchBooks(String text, String searchType) {
		Statement stmt;
		Search search = new Search();
		Search results = new Search();
		Books books = new Books();
		try {	
			stmt = EchoServer.conn.createStatement();
			ResultSet rs = null;
			if(searchType.equals("0"))
			{
				rs = stmt.executeQuery("SELECT * FROM books WHERE BookName='"+ text+"';");
			}
			else
			{
				if(searchType.equals("1"))
				{
					rs = stmt.executeQuery("SELECT * FROM books WHERE Scope='"+ text+"';");
				}
					else
				{
					if(searchType.equals("2"))
					{
						rs = stmt.executeQuery("SELECT * FROM books;");
						while(rs.next())
				 		{
							String n = rs.getString(2);
							String s = rs.getString(6);
							String d = rs.getString(7);
							if (n.contains(text)||s.contains(text)||d.contains(text))
							{
								
									books = new Books(rs.getInt(1),rs.getString(2),rs.getString(3),rs.getInt(4),rs.getString(5),rs.getString(6),rs.getString(7),rs.getInt(8),rs.getInt(9),rs.getString(10),rs.getInt(11),"Not exists");
									search.booksList.add(books);	
							}
						}
						results = returnResults(search);
						return search;
					}
				}
			}
			while(rs.next())
	 		{	
				books = new Books(rs.getInt(1),rs.getString(2),rs.getString(3),rs.getInt(4),rs.getString(5),rs.getString(6),rs.getString(7),rs.getInt(8),rs.getInt(9),rs.getString(10),rs.getInt(11),"Not exists");
				search.booksList.add(books);	
			} 
			results = returnResults(search);
			return results;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return results;
	}
	
	/** @param search instance of Search
	 * @return instance of Search with search details*/
private static Search returnResults(Search search) {
	Statement stmt;
	try {	
			stmt = EchoServer.conn.createStatement();
			for (int i = 0 ; i<search.booksList.size(); i++)
			{
				int id = search.booksList.get(i).getBookId();
				ResultSet rs2 = stmt.executeQuery("SELECT count(copyId) FROM copies WHERE bookId='"+id+"';");
				int r2=-1;
				int r3=-1;
				if (rs2.next())
					r2 = rs2.getInt(1);
				ResultSet rs3 = stmt.executeQuery("SELECT count(copyId) FROM copies WHERE availability='0' && bookId='"+id+"';");
				if (rs3.next())
					r3 = rs3.getInt(1);
			
				if (r2==r3)
				{	
					ResultSet rs1 = stmt.executeQuery("SELECT l.returnDate FROM loans l,copies c WHERE c.copyId=l.copyId && c.bookId='"+id+"' group by l.returnDate;");
					if (rs1.next())
						search.booksList.get(i).setReturnDate(rs1.getString(1));
				}
				else
				{
					search.booksList.get(i).setReturnDate("availabile");
				}
			}	
		} catch (SQLException e) {
			e.printStackTrace();
		}
	return search;
}

	/**
	 * @return ArrayList<String[]> of reports*/
	public static ArrayList<String[]> getReports()
	{
		ArrayList<String[]> reports=new ArrayList<String[]>();
		Statement stmt;
		try {	
			stmt = EchoServer.conn.createStatement();
			ResultSet rs = stmt.executeQuery("SELECT * FROM reports;");
	 		while(rs.next())
	 		{
	 			String s[]={rs.getString(1),rs.getString(3)};
	 			reports.add(s);
			} 
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return reports;
	}

	/**
	 * @param reportId report id of wanted id to display
	 * @return path of file report*/
	public static byte[]  getReport(String reportId) {
		int rId=Integer.parseInt(reportId);
        Statement stmt;	
        byte[] fileBytes=null;
		try {
			stmt = EchoServer.conn.createStatement();
			ResultSet rs = stmt.executeQuery("SELECT file from reports WHERE reportId='"+rId+"';");
			 if (rs.next()) {
		        	String path=rs.getString(1);
		        	File f=new File(path);
					 InputStream inputStream = null;
					try {
						inputStream = new FileInputStream(f);
					} catch (FileNotFoundException e) {
						// TODO Auto-generated catch block
						e.printStackTrace();
					} 
					fileBytes = new byte[(int) f.length()];
					 try {
						inputStream.read(fileBytes);
					} catch (IOException e) {
						// TODO Auto-generated catch block
						e.printStackTrace();
					}   
		            }
				} catch (SQLException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}
				return (fileBytes);
	}
	
	/**
	 * @return ArrayList<String[]> of number of reader cards of each status*/
	public static ArrayList<String[]> countByReaderCardStatus()
	{
		ArrayList<String[]> numOf=new ArrayList<String[]>();
		Statement stmt;
		try {	
			stmt = EchoServer.conn.createStatement();
			ResultSet rs = stmt.executeQuery("SELECT status,COUNT(status) FROM readercard GROUP BY status;");
	 		while(rs.next())
	 		{
	 			String s[]={rs.getString(1),rs.getString(2)};
	 			numOf.add(s);
			} 
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return numOf;
	}
	
	/***
	 * @return ArrayList<String[]> of books that readers where late in returning them*/
	public static ArrayList<String[]> avgForNumberOfLatesAndDurationReport()
	{
		Statement stmt;
		int f=0;
		ArrayList<String[]> list=new ArrayList<String[]>();
		try {	
			DateFormat dateFormat = new SimpleDateFormat("yyyy/MM/dd");
			stmt = EchoServer.conn.createStatement();
			ResultSet rs = stmt.executeQuery("SELECT l.returnDate,l.copyId,c.bookId FROM libary.loans l,libary.copies c WHERE l.copyId=c.copyId;");
			while(rs.next())
	 		{
	 			String returnDate=rs.getString(1);
				Date rDate=dateFormat.parse(returnDate);
				Calendar cal = Calendar.getInstance();
				if(cal.getTime().after(rDate))//if loan is still in loans table and day of return passed
				{
					for(int i=0;i<list.size();i++)
					{
						if(list.get(i)[0].equals(Integer.toString(rs.getInt(3))))
						{
							String today=dateFormat.format(cal.getTime());
							String reDate=dateFormat.format(rDate);
							long newNum=Long.parseLong(list.get(i)[1])+1;
							long newDu=diffDate(reDate,today)+Long.parseLong(list.get(i)[2]);
							String[] s={Integer.toString(rs.getInt(3)),Long.toString(newNum),Long.toString(newDu)};//s[0]=id,s[1]=numOfLate,s[2]=duOfLate
							list.set(i,s);
							f=1;
							break;
						}
					}
					if(f==0)
					{
						String today=dateFormat.format(cal.getTime());
						String reDate=dateFormat.format(rDate);
						String[] s={Integer.toString(rs.getInt(3)),"1",Long.toString(diffDate(reDate,today))};
						list.add(s);
					}
				}
	 		}
			f=0;
			rs = stmt.executeQuery("SELECT lh.returnDate,lh.reallyReturnDate,lh.copyId,b.bookId,c.copyId,COUNT(b.bookId) FROM loanshistory lh,books b,copies c WHERE lh.copyId=c.copyId AND c.bookId=b.bookId GROUP BY b.bookId;");
			while(rs.next())
		 	{
				String returnDate=rs.getString(1);
				String realDate=rs.getString(2);
				Date rDate=dateFormat.parse(returnDate);
				Date rrDate=dateFormat.parse(realDate);
				if(rrDate.after(rDate))//if there was late in return
				{
					for(int i=0;i<list.size();i++)
					{
						if(list.get(i)[0].equals(Integer.toString(rs.getInt(4))))
						{
							String rrDateS=dateFormat.format(rrDate);
							String reDate=dateFormat.format(rDate);
							long sum=Long.parseLong(list.get(i)[1])+1;
							long newDu=diffDate(reDate,rrDateS)+Long.parseLong(list.get(i)[2]);
							String s[]={list.get(i)[0],Long.toString(sum),Long.toString(newDu)};
							list.set(i,s);
							f=1;
							break;
						}
					}
					if(f==0)
					{
						String rrDateS=dateFormat.format(rrDate);
						String reDate=dateFormat.format(rDate);
						String[] s={Integer.toString(rs.getInt(3)),"1",Long.toString(diffDate(reDate,rrDateS))};
						list.add(s);
					}
				}
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		catch (ParseException e) {
			e.printStackTrace();
		}
		return list;
	}
	
	/**
	 * @param from calculate number of loaned copies from this date
	 * @param to calculate number of loaned copies until this date
	 * @return number of loaned copies during a specific time*/
	public static int countByLoanedCopies(String from,String to)
	{
		ArrayList<String[]> numOf=new ArrayList<String[]>();
		Statement stmt;
		int res=0;
		try {	
			stmt = EchoServer.conn.createStatement();
			ResultSet rs = stmt.executeQuery("SELECT l.loanDate FROM loans l,copies c WHERE l.copyId=c.copyId");
	 		while(rs.next())
	 		{
	 			String loanDate=rs.getString(1);
				DateFormat dateFormat = new SimpleDateFormat("yyyy/MM/dd");
				Date loanDateD=null;
				Date fromDate=null;
				Date toDate=null;
				try {
					loanDateD=dateFormat.parse(loanDate);
					fromDate=dateFormat.parse(from);
					toDate=dateFormat.parse(to);
				} catch (ParseException e) {
					e.printStackTrace();
				}
	 			if((loanDateD.after(fromDate))&&(loanDateD.before(toDate)))
	 				res++;
			} 
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return res;
	}
	
	/**
	 * @param from calculate number of readers that are late in returning books from this date
	 * @param to calculate number of readers that are late in returning books until this date
	 * @return number of readers that are late in returning books during a specific time*/
	public static int countLateInReturns(String from,String to)
	{
		HashSet<Integer> res=new HashSet<Integer>();
		Statement stmt;
		try {	
			DateFormat dateFormat = new SimpleDateFormat("yyyy/MM/dd");
			Date fDate=dateFormat.parse(from);
			Date tDate=dateFormat.parse(to);
			stmt = EchoServer.conn.createStatement();
			ResultSet rs = stmt.executeQuery("SELECT l.readerId,l.returnDate FROM loans l;");
			while(rs.next())
	 		{
	 			String returnDate=rs.getString(2);
				Date rDate=dateFormat.parse(returnDate);
				if(((rDate.after(fDate))||(rDate.equals(fDate)))&&((rDate.before(tDate))||(rDate.equals(tDate))))
				{
					Calendar cal = Calendar.getInstance();
					if(cal.getTime().after(rDate))//if loan is still in loans table and day of return passed
					{
						res.add(rs.getInt(1));
					}
				}
	 		}
			rs = stmt.executeQuery("SELECT lh.readerId,lh.returnDate,lh.reallyReturnDate from loanshistory lh;");
			while(rs.next())
		 	{
				String returnDate=rs.getString(2);
				String realDate=rs.getString(3);
				Date rDate=dateFormat.parse(returnDate);
				Date rrDate=dateFormat.parse(realDate);
				if((((rDate.after(fDate))||(rDate.equals(fDate)))&&((rDate.before(tDate))||(rDate.equals(tDate))))||(((rrDate.after(fDate))||(rrDate.equals(fDate)))&&((rrDate.before(tDate))||(rrDate.equals(tDate)))))
				{
					if(rrDate.after(rDate)) res.add(rs.getInt(1));
				}
		 	}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		catch (ParseException e) {
			e.printStackTrace();
		}
		return res.size();
	}
	
	/**
	 * get readers
	 * @return ArrayList<String[]> of reader cards and their details
	 */
	public static ArrayList<String[]> getReaders() {
		ArrayList<String[]> readers=new ArrayList<String[]>();
		Statement stmt;
		try {	
			stmt = EchoServer.conn.createStatement();
			ResultSet rs = stmt.executeQuery("SELECT * FROM readercard;");
	 		while(rs.next())
	 		{
	 			String s[]={rs.getString(1),rs.getString(5),rs.getString(6),rs.getString(7)};
	 			readers.add(s);
			} 
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return readers;
	}
	
	/**
	 * @param readerId reader card id
	 * @return ArrayList<String[]> of reader card details*/
	public static ArrayList<String[]> getReaderCardDetails(String readerId) {
		ArrayList<String[]> reader=new ArrayList<String[]>();
		Statement stmt;
		int rId=Integer.parseInt(readerId);
		try {	
			stmt = EchoServer.conn.createStatement();
			ResultSet rs = stmt.executeQuery("SELECT * FROM readercard WHERE readerId='"+rId+"';");
	 		while(rs.next())
	 		{
	 			String s[]={rs.getString(1),rs.getString(2),rs.getString(3),rs.getString(4),rs.getString(5),rs.getString(6),rs.getString(7)};
	 			reader.add(s);
			} 
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return reader;
	}
	
	/**
	 * @param wId worker id to display his details
	 * @return worker's number wId details*/
	public static ArrayList<String[]> getWorkerData(String wId)
	{
		Statement stmt;
		ArrayList<String[]> worker=new ArrayList<String[]>();
		int workerId=Integer.parseInt(wId);
		try {	
			stmt = EchoServer.conn.createStatement();
			ResultSet rs = stmt.executeQuery("SELECT * FROM worker WHERE workerId="+workerId+";");
	 		if(rs.next())
	 		{
	 			String s[]={Integer.toString(rs.getInt(1)),rs.getString(2),rs.getString(3),rs.getString(4),rs.getString(5),rs.getString(6)};
	 			worker.add(s);
			} 
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return worker;
	}
	/**
	 * This method return Reader Card Student
	 * @param userName
	 * @return the  ReaderCard
	 */
	public static ReaderCard getReaderCardStudent(String userName) {
		Statement stmt;
		String userN = userName;
		ReaderCard readercard = new ReaderCard();

		try {	
			stmt = EchoServer.conn.createStatement();
			ResultSet rs = null;
		    rs = stmt.executeQuery("SELECT * FROM readercard WHERE userName='"+userN+"';");
 			System.out.print("SELECT * FROM readercard WHERE userName='"+userN+"';");

	 		if(rs.next())
	 		{
	 			readercard = new ReaderCard(rs.getInt(1),rs.getString(2),rs.getString(3),rs.getString(4),rs.getString(5),rs.getString(6),rs.getString(7));
			} 
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return readercard;
	}
	
	/**
	 * @param readerId reader card id
	 * @param userName user name of reader
	 * @param phone
	 * @param eamil
	 * @param firstName
	 * @param lastName
	 * @param status
	 * @return true if edit succeeded, else false
	 * this method edit a reader card*/
	public static boolean editCard(String readerId, String userName, String phone, String email, String firstName, String lastName, String status) {

		Statement stmt;
		PreparedStatement update;
		int rId=Integer.parseInt(readerId);
		
		    try {
			update=EchoServer.conn.prepareStatement("UPDATE readercard SET userName=?,phone=?,email=?,firstName=?,lastName=?,status=? WHERE readerId=?;");
			update.setString(1,userName);
			update.setString(2,phone);
			update.setString(3,email);
			update.setString(4,firstName);
			update.setString(5,lastName);
			update.setString(6,status);
			update.setInt(7,rId);
			update.execute();
			
			} catch (SQLException e) {
				return false;
			} 
			
		return true;
	}
	/**
	 * extend book
	 * @param loanId
	 * @param userName
	 * @param who who make the extend : librerian/student
	 * @return
	 */
	public static ArrayList<String[]> extendManual(String loanId,String userName,String who) {
		Statement stmt;
		ArrayList<String[]> back=new ArrayList<String[]>();
		int lId=Integer.parseInt(loanId);	
		int copyId=0;
		try 
		{	
			stmt = EchoServer.conn.createStatement();
			ResultSet rs = stmt.executeQuery("SELECT * FROM loans WHERE loanId='"+lId+"';");
			if(rs.next())
			{
				copyId=rs.getInt(5);
			if(!(checkNotOrderd(rs.getInt(5))))
			{
				String[] s={"orderd"};
				back.add(s);
				return (back);
			
			}
			String loan=rs.getString(3);
			String returnDate=rs.getString(4);
			Calendar cal = Calendar.getInstance();
			DateFormat dateFormat = new SimpleDateFormat("yyyy/MM/dd");
			Date date2 = null;
			try {
				date2 = dateFormat.parse(returnDate);
			} catch (ParseException e1) {
				// TODO Auto-generated catch block
				e1.printStackTrace();
			}
			if(dontLate(date2))
			{
			if(checkWeekBefore(date2))
			{
			int rank=getPopularity(rs.getInt(5));
			
			cal.setTime(date2);
			if(rank==1)
			cal.add(Calendar.DATE, 3);
			else cal.add(Calendar.DATE, 14);
			loan=dateFormat.format(cal.getTime());
			
			PreparedStatement update=EchoServer.conn.prepareStatement("UPDATE loans SET returnDate=? WHERE loanId=?");
			update.setString(1,loan);
			update.setInt(2, lId);
			update.execute();
			
			if(who.equals("librerian"))
			{
		     update=EchoServer.conn.prepareStatement("UPDATE loans SET returnDate=?,nameExtend=?,dateExtend=? WHERE loanId=?");
		    update.setString(1,loan);
			update.setInt(4, lId);
			Calendar cal2 = Calendar.getInstance();
			String  today = dateFormat.format(cal2.getTime());
			update.setString(3,today);
			System.out.println(userName);
			String libName=getLibName(userName);
			System.out.println(libName);
			update.setString(2,libName);
			update.execute();
			String rId=Integer.toString(rs.getInt(2));
			return extendLoan(rId);
			}
			update=EchoServer.conn.prepareStatement("INSERT INTO messages VALUES(?,?,?,?)");
			update.setInt(1,getNextMsgId());
			DateFormat date = new SimpleDateFormat("yyyy/MM/dd");
		    Calendar cal2 = Calendar.getInstance();
		    String  today = date.format(cal2.getTime());
			String msg=userName+" extended copy number "+copyId+" of book number "+matchCidToBid(copyId)+" at "+today;
			update.setString(2,msg);
			update.setString(3,today);
			update.setInt(4,0);
			update.execute();
			String rId=Integer.toString(rs.getInt(2));
			return extendLoan(rId);
			}
			String[] s={"week"};
			back.add(s);
			return (back);
			}
			String[] s={"late"};
			back.add(s);
			return (back);
			}
		} catch (SQLException e) {}
		return back;
		
	

	}
	/**
	 * check if the reader don't late with his book
	 * @param date2 return date
	 * @return
	 */
	private static boolean dontLate(Date date2) {
		Calendar cal = Calendar.getInstance();			
		if(date2.before(cal.getTime())) return false;
		else return true;
	}
	/**
	 * check if there is less then week until return the book
	 * @param date2 return date
	 * @return
	 */
	private static boolean checkWeekBefore(Date date2) {
		Calendar cal = Calendar.getInstance();			
		cal.add(Calendar.DATE, 7);
		if(date2.after(cal.getTime())) return false;
		else return true;
		
	}
	
	/**
	 * @return next message id if message to librarian*/
	public static int getNextMsgId()
	{
		Statement stmt;
		try {
			stmt = EchoServer.conn.createStatement();
			ResultSet rs = stmt.executeQuery("SELECT * FROM messages;");
			rs.last();
			if(rs.last()) return (rs.getInt(1)+1);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}

		return 1;
	}
	
	/**
	 * @return ArrayList<String[]> of messages on books that their loan duration was extended 
	 * by the reader*/
	public static ArrayList<String[]> getMsgsOnExtendedBooks()
	{
		ArrayList<String[]> msgs=new ArrayList<String[]>();
		Statement stmt;
		PreparedStatement update;
		try {
	    stmt = EchoServer.conn.createStatement();
	    ResultSet rs=stmt.executeQuery("SELECT * FROM messages WHERE status='0';");
	    while(rs.next())
	    {
	    	String msg[]={rs.getString(2)};
	    	msgs.add(msg);
	    	update=EchoServer.conn.prepareStatement("UPDATE messages SET status=? WHERE msgId=?;");
	    	update.setInt(1,1);
	    	update.setInt(2,rs.getInt(1));
	    	update.execute();
	    	System.out.println(rs.getInt(4));
	    }
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return msgs;
		
	}
	
	/**
	 * @param cId copy id of copy to check if ordered
	 * @return true if not ordered, else false*/
	private static boolean checkNotOrderd(int cId) {
		Statement stmt;
		try {
	    stmt = EchoServer.conn.createStatement();
	    ResultSet rs=stmt.executeQuery("SELECT * FROM orders where status='wait' OR status='available';");
	    
		while(rs.next())
		{
			if(rs.getInt(3)==cId)
		{
			System.out.println(rs.getString(5)+" "+rs.getInt(3));
			return false;	
		}
		}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		 return true;
	}
	
	/**
	 * @param userName user name of librarian
	 * @return name of librarian with the given userName*/
	private static String getLibName(String userName) {
			Statement stmt;
			try {
			stmt = EchoServer.conn.createStatement();
			System.out.println(userName);
			ResultSet rs=stmt.executeQuery("SELECT * FROM worker where userName='"+userName+"';");
			if(rs.next())
			{
			String lib=rs.getString(2)+" "+rs.getString(3);
			return lib;	
			}
	
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
			return null;
	
	
	
	}
	
	
	/**
	 * 
	 * @param cId copy id
	 * @return
	 */
	private static  int getPopularity(int cId) {
	   
		Statement stmt;
		try
		{
			stmt = EchoServer.conn.createStatement();
			ResultSet rs=stmt.executeQuery("SELECT bookId FROM copies where copyId='"+cId+"';");
			rs.next();
			int bid=rs.getInt(1);
			System.out.println("SELECT rank FROM Books where BookId='"+bid+"';");
			 rs=stmt.executeQuery("SELECT rank FROM Books where BookId='"+bid+"';");
			 rs.next();
			 return (rs.getInt(1));
		}
		catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return -1;
	
	}
	
	/**
	 * This method finds the loans of student ,using the userName
	 * @param userName
	 * @param loanType:0-active loans 
	 *                 1-history loans
	 * @return the  history loans  or active loans 
	 */
	public static History LendingHistory(String userName,String loanType) {
		Statement stmt;
		History history = new History();
		LendingHistoryStudent loan = new LendingHistoryStudent();
		
		
		try {	
			stmt = EchoServer.conn.createStatement();
			ResultSet rs = null;
			if(loanType.equals("0"))
			{
				rs = stmt.executeQuery("SELECT * FROM  readercard r, copies c, books b,loans l  WHERE l.copyId=c.copyId && b.bookId=c.bookId && r.readerId=l.readerId && r.userName='"+ userName+"';");				
			}
			else
			{
				rs = stmt.executeQuery("SELECT * FROM readercard r, copies c, books b ,loanshistory lh  WHERE lh.copyId=c.copyId && b.bookId=c.bookId && r.readerId=lh.readerId && r.userName='"+ userName+"';");	
			}
			
			while(rs.next())
	 		{
				loan = new LendingHistoryStudent(rs.getString(12),rs.getString(26),rs.getString(27),rs.getInt(8));
				history.historyList.add(loan);	
			} 
			return history;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return history;
	}
	/**
	 * This method finds the delays of student ,using the userName
	 * @param userName
	 * @return the History
	 */
	public static History LateReturn(String userName) {
		Statement stmt;
		History history1 = new History();
		LendingHistoryStudent loan = new LendingHistoryStudent();
		try {	
			stmt = EchoServer.conn.createStatement();
			ResultSet rs = null;
			 DateFormat dateFormat = new SimpleDateFormat("yyyy/MM/dd");
			    Calendar cal = Calendar.getInstance();
			    String  today = dateFormat.format(cal.getTime());
			    System.out.println(today);
			rs = stmt.executeQuery("SELECT * FROM  readercard r, copies c, books b,loans l  WHERE l.copyId=c.copyId && b.bookId=c.bookId && r.readerId=l.readerId && l.returnDate<'"+today+"' && r.userName='"+ userName+"';");				
			while(rs.next())
	 		{
				loan = new LendingHistoryStudent(rs.getString(12),rs.getString(26),rs.getString(27),rs.getInt(8));
				history1.historyList.add(loan);	
			} 
			return history1;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return history1;
	}
	
	/**
	 * This method get ReaderId
	 * @param userName
	 * @return the readerId
	 */
	public static String getReaderId(String userName) {
		Statement stmt;		
			try {
			stmt = EchoServer.conn.createStatement();
			ResultSet rs=stmt.executeQuery("SELECT readerId FROM readercard where userName='"+userName+"';");
			if (rs.next())
				return (rs.getString(1));
			
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
			return ("error");
	}

	/**
	 * @param readerId reader card id
	 * @param firstName
	 * @param phone
	 * @param eamil
	 * @param lastName
	 * @return true if edit succeeded, else false
	 * this method edit personal details of reader card*/
	public static boolean editPersonalDetails(String readerId,String firstName,String phone,String email,String lastNam) {
     
	Statement stmt;
	int rId=Integer.parseInt(readerId);

	try 
	{
		PreparedStatement update=EchoServer.conn.prepareStatement("UPDATE readercard SET LASTNAME=?,FIRSTNAME=?,EMAIL=?,PHONE=? WHERE readerId=?;");
		update.setString(1,lastNam );
		update.setString(2,firstName );
		update.setString(3,email );
		update.setString(4,phone );

		update.setInt(5,rId);
		update.execute();

		return true;
	} catch (SQLException e) {e.printStackTrace();}

	return false;

}
	
	/**
	 * @param copy copy id of copy to return
	 * @return message that copy is back to shelf
	 * this method returns a copy*/
	public static String returnCopy(String copy) {
		int cId=Integer.parseInt(copy);
		Statement stmt;
		
		try {
		stmt = EchoServer.conn.createStatement();
		ResultSet rs = stmt.executeQuery("SELECT * FROM loans WHERE copyId='"+cId+"';");
		if(rs.next())
 		{
			int rId=rs.getInt(2);
			PreparedStatement update=EchoServer.conn.prepareStatement("INSERT INTO loanshistory VALUES(?,?,?,?,?,?)");
		    update.setInt(1,rs.getInt(1));
			update.setInt(2,rs.getInt(2));
			update.setString(3,rs.getString(3));
			update.setString(4,rs.getString(4));
			update.setInt(5,rs.getInt(5));
		   
			DateFormat dateFormat = new SimpleDateFormat("yyyy/MM/dd");
			Calendar cal = Calendar.getInstance();
			String  today = dateFormat.format(cal.getTime());
			update.setString(6,today);
			update.execute();
			
			 update=EchoServer.conn.prepareStatement("DELETE FROM loans WHERE loanId='"+rs.getInt(1)+"';");
			 update.execute();
			 if(!updateStudent(copy))
				 changeAviability(copy);
			 ResultSet rs3 = stmt.executeQuery("SELECT * FROM loans WHERE readerId='"+rId+"';");
			 ResultSet rs2=stmt.executeQuery("SELECT * FROM readercard WHERE readerId='"+rId+"';");
			 if(rs3==null)
			 {
				 System.out.println(rId);
				 if(rs2.getString(7).equals("freezeUntil"))
				 {
					 changeReaderCardStatus(rId,"not active");
				 }
			 }
 		} 
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return ("Book Copy back to shelf");	
	}
	
	/**
	 * @param copy copy id
	 * @return true if sending mail succeeded, else false
	 * thos method update student by mail that is order has arrived*/
	private static boolean updateStudent(String copy) {
		Statement stmt;
	
			int cId=Integer.parseInt(copy);
			try {
				
			stmt = EchoServer.conn.createStatement();
			ResultSet rs = stmt.executeQuery("SELECT * FROM orders WHERE copyId='"+ cId+"';");
			if (rs.next())
			{
				PreparedStatement update=EchoServer.conn.prepareStatement("UPDATE orders SET dateArrive=?,status=? WHERE  copyId='"+ cId+"';");
			
				
			    DateFormat dateFormat = new SimpleDateFormat("yyyy/MM/dd");
			    Calendar cal = Calendar.getInstance();
			    String  today = dateFormat.format(cal.getTime());
				update.setString(1, today);
				update.setString(2, "available");
				update.execute();
				String BookName=getBookName(cId);
				sendMail(rs.getInt(2),"Book arrived","book"+" "+BookName+" "+"waiting for you in the library for 2 days");
				return true;
			}
		
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
			return false;
	}
	
	/**
	 * @param copy copy id
	 * @return book name that the given copy is copy of it*/
	private static String getBookName(int copy) {
		Statement stmt;	
		try {
			stmt = EchoServer.conn.createStatement();
		ResultSet rs = stmt.executeQuery("SELECT * FROM copies WHERE copyId='"+ copy+"';");
		if(rs.next())
		{	
		ResultSet rs2 = stmt.executeQuery("SELECT * FROM books WHERE bookId='"+ rs.getInt(2)+"';");
		if (rs2.next())
		{
			return rs2.getString(2);
		}
		}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return null;
	}
	
	/**
	 * This method insert to books list if BookId= text
	 * @param text
	 * @return the Books
	 */
	public static Books OrderBook(String text) {
		Statement stmt;
		Books books = new Books();
		try {	
			stmt = EchoServer.conn.createStatement();
			ResultSet rs = null;
			rs = stmt.executeQuery("SELECT * FROM books WHERE BookId='"+ text+"';");
			
			
			
			if(rs.next())
				books = new Books(rs.getInt(1),rs.getString(2),rs.getString(3),rs.getInt(4),rs.getString(5),rs.getString(6),rs.getString(7),rs.getInt(8),rs.getInt(9),rs.getString(10),rs.getInt(11),null);
			return books;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return books;
	}
	/**
	 * This method checks all the activity orders of the student's 
	 * @param userName
	 * @return the ListOrderBookStudent
	 */
	public static ListOrderBookStudent  getListOrderBookStudent(String userName) {
		Statement stmt;
		ListOrderBookStudent listOrderBookStudent = new ListOrderBookStudent();
		OrderBookStudent orderB = new OrderBookStudent();
		try {	
			stmt = EchoServer.conn.createStatement();
			ResultSet rs = null;
		 	String notActive="not active";
			rs = stmt.executeQuery("SELECT * FROM readercard r, copies c, books b ,orders o  WHERE o.copyId=c.copyId && b.bookId=c.bookId && r.readerId=o.readerId && o.status!='"+notActive+"' && r.userName='"+ userName+"';");	
		
			while(rs.next())
	 		{
				orderB = new OrderBookStudent(rs.getString(12),rs.getInt(26),rs.getString(27),rs.getString(28));
				listOrderBookStudent.ListOrderBookStudent.add(orderB);	
			} 
			return listOrderBookStudent;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return listOrderBookStudent;
	}
	
	/**
	 * @param string book id
	 * @return byte array of table of content of the given book*/
	public static byte[]  getcontent(String string) {

		
		byte[] fileBytes=null;
		int bId=Integer.parseInt(string);
        String query;
        Statement stmt;		
		try {
			stmt = EchoServer.conn.createStatement();
	
		ResultSet rs = stmt.executeQuery("SELECT content from books WHERE BookId='"+bId+"';");
        if (rs.next()) {
        	String path=rs.getString(1);
        	File f=new File(path);
			 InputStream inputStream = null;
			try {
				inputStream = new FileInputStream(f);
			} catch (FileNotFoundException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			} 
			fileBytes = new byte[(int) f.length()];
			 try {
				inputStream.read(fileBytes);
			} catch (IOException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}   
            }
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return (fileBytes);
		
	}


/**
 * @param type type of report
 * @param path path of saved file report
 * @return true if adding report to db succeeded, else false*/
public static boolean addReport(Report r)
{
	FileWriter fw;
	try {
		PreparedStatement update = EchoServer.conn.prepareStatement("INSERT INTO reports VALUES(?,?,?);");
		update.setInt(1,getNextReportId());
		update.setString(2,r.getType());
		String path="c:/files/"+r.getType()+"From"+r.getFrom()+"To"+r.getTo()+".txt";
		fw=new FileWriter(path);
		fw.write(r.getFile());
		fw.close();
		update.setString(3,path);
		update.execute();
	} catch (SQLException e1) {
		// TODO Auto-generated catch block
		e1.printStackTrace();
		return false;
	}
	catch (IOException e) {
	// TODO Auto-generated catch block
	e.printStackTrace();
	}
	return true;
}

	
	/**
	 * @return ArrayList<String[]> of workers and their details*/
	public static ArrayList<String[]> getWorkersData()
	{
		ArrayList<String[]> workers=new ArrayList<String[]>();
		Statement stmt;
		try {	
			stmt = EchoServer.conn.createStatement();
			ResultSet rs = stmt.executeQuery("SELECT * FROM worker;");
	 		while(rs.next())
	 		{
	 			String s[]={Integer.toString(rs.getInt(1)),rs.getString(2),rs.getString(3),rs.getString(4),rs.getString(5),rs.getString(6)};
	 			workers.add(s);
			} 
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return workers;
	}
	/**
	 * This method checks if the  user can order a book.
	 * @param bookId
	 * @param userName
	 * @return the 0-There are copies available
	 *             1-All the copies are already ordered
	 *             2-Card is not available
	 *             3-Order added
	 *             4-Error
	 */
	public static int CheckOrderBook(String bookId, String userName) {
		int bId=Integer.parseInt(bookId);
	
		try {
			
			Statement stmt;
			stmt = EchoServer.conn.createStatement();
			ResultSet rs = null;
			int numCopies;
			rs = stmt.executeQuery("SELECT * FROM  books b WHERE b.bookId='"+ bId+"';");
			rs.next();
			numCopies=rs.getInt(9);
			rs = stmt.executeQuery("SELECT COUNT(c.bookId) FROM  books b ,copies c, loans l WHERE b.BookId=c.BookId && c.copyId=l.copyId &&b.bookId='"+ bId+"';");	
            int countBooksLoans;
        	rs.next();
            countBooksLoans=rs.getInt(1);
            int numOfAvilabelCopies;
            numOfAvilabelCopies= numCopies-countBooksLoans;
            if(numOfAvilabelCopies>0)
            	return 0; 	//There are copies available
			String notActive="not active";
            rs = stmt.executeQuery("SELECT COUNT(c.bookId) FROM  books b ,copies c, orders o WHERE b.BookId=c.BookId && c.copyId=o.copyId && o.status!='"+notActive+"' && b.bookId='"+ bId+"';");	
            int numOfOrders;
        	rs.next();
            numOfOrders=rs.getInt(1);
            if(numOfOrders==numCopies)
            	return 1;	//All the copies are already ordered
			rs = stmt.executeQuery("SELECT status FROM  readercard r WHERE r.userName='"+userName+"';");	
        	rs.next();
        	if (!(rs.getString(1).equals("active")))
        		return 2;
            else
            {
            	String notActive1="not active";
    			rs = stmt.executeQuery("SELECT copyId FROM  libary.copies c WHERE bookId='"+bId+"' && NOT EXISTS (select copyId from libary.orders o WHERE c.copyId=o.copyId&& o.status!='"+notActive1+"');");
    			PreparedStatement update=EchoServer.conn.prepareStatement("INSERT INTO orders VALUES(?,?,?,?,?,?)");
    			rs.next();
    			update.setInt(3,rs.getInt(1));
    			update.setString(5,"wait");
    			rs = stmt.executeQuery("SELECT max(orderId) FROM  orders;");
    			rs.next();
    			System.out.print(rs.getInt(1));
    			update.setInt(1,rs.getInt(1)+1);
    			DateFormat dateFormat = new SimpleDateFormat("yyyy/MM/dd");
    			Calendar cal = Calendar.getInstance();
    			String  today = dateFormat.format(cal.getTime());
    			update.setString(4,today);
    			rs = stmt.executeQuery("SELECT readerId FROM readercard r WHERE r.userName='"+userName+"';");
    			rs.next();
    			System.out.print(rs.getInt(1));
    			update.setInt(2,rs.getInt(1));
    			update.setString(6,"   ");

    			update.execute();
    			return 3;	// order added
            }
		    
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return  4;
	}

	/**
	 * @param today date of today
	 * this method sends email to readers if they need to return book tomorrow*/
	public static void TomorrowRetrunBook(Date today) {
		
		DateFormat dateFormat = new SimpleDateFormat("yyyy/MM/dd");
		Calendar cal = Calendar.getInstance();
		
	   Statement stmt;
	
			try {
				try {
					stmt = EchoServer.conn.createStatement();
			
			ResultSet rs = null;
			rs = stmt.executeQuery("SELECT * FROM loans;");
			while (rs.next())
			{
			String dateReturn=rs.getString(4);
			Date returnDate=dateFormat.parse(dateReturn);
			cal.setTime(today);
			cal.add(Calendar.DATE, 1);
			
			if(cal.getTime().compareTo(returnDate)==0)
				{
				sendMail(rs.getInt(2),"return Book","hello, you need return tomorrow copy"+" "+rs.getString(5));
				
				}
			}
			
				} catch (SQLException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}
			
			} catch (ParseException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
				
				
}
		
	/**
	 * @param readerId reader id
	 * @param subject
	 * @param text text of mail
	 * this method sends email to reader number readerId with given subject and text*/
	private static void sendMail(int readerId,String subject,String text) {
			Statement stmt;
			try {
				stmt = EchoServer.conn.createStatement();
				ResultSet rs = null;
				rs = stmt.executeQuery("SELECT email FROM readercard WHERE readerId='"+readerId+"';");
				if(rs.next())
				{
				System.out.println(rs.getString(1));
			     SendMail sm=new SendMail("Maor",rs.getString(1),subject,text);
			     sm.send();
			     
					
				}
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		
	}

   /**
    * This method Check Reader Card Student
    * @param userName
    * @return the 0 -existing reader card 
    *             1 -User does not exist Reader Card 
    */
	public static int CheckReaderCardStudent(String userName) {
		Statement stmt;
		
		try {	
			stmt = EchoServer.conn.createStatement();
			ResultSet rs = null;
			rs = stmt.executeQuery("SELECT readerId FROM readercard WHERE userName='"+userName+"';");
			
			
			if(!(rs.next()))
				return 0;	
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return 1;
	}
	
	/**
	 * @param today date of today
	 * this method changes status of order*/
	public static void bookDontWait(Date today) {
		DateFormat dateFormat = new SimpleDateFormat("yyyy/MM/dd");
		Calendar cal = Calendar.getInstance();
		
		
	   Statement stmt;
	
			try {
			try {
				stmt = EchoServer.conn.createStatement();
			
			ResultSet rs = stmt.executeQuery("SELECT * FROM orders;");
			while (rs.next())
			{
			if((rs.getString(6) != null)&&(!(rs.getString(6).equals("   "))))
			{
			String dateArrive=rs.getString(6);
			Date date=dateFormat.parse(dateArrive);
			cal.setTime(date);
			cal.add(Calendar.DATE, 2);
			if(cal.getTime().compareTo(today)==0)
				{
				
				try 
				{
					PreparedStatement update=EchoServer.conn.prepareStatement("UPDATE orders SET STATUS=? WHERE readerId=?");
					update.setString(1,"not active");
					update.setInt(2, rs.getInt(2));
					update.execute();
					changeAviability(rs.getString(3));
				} catch (SQLException e) {e.printStackTrace();}
				
				}
			}
			}
			} catch (SQLException e1) {
				// TODO Auto-generated catch block
				e1.printStackTrace();
			}
			
			} catch (ParseException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
			
	}
	/**
	 * This method get userName
	 * @param readerId
	 * @return the userName
	 */
	public static String getuserName(String readerId) {
	
		Statement stmt;	
		try {
			stmt = EchoServer.conn.createStatement();
		ResultSet	rs = stmt.executeQuery("SELECT userName FROM readercard WHERE readerId='"+readerId+"';");
		if(rs.next())
			return rs.getString(1);
		
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		 return null;
		
			
	}
	
	/**
	 * This method calculates the difference between two dates
	 * @param date1
	 * @param date2
	 * @return the difference
	 */
	public static long diffDate(String date1, String date2)
	 {
	    Calendar cal1 = Calendar.getInstance();
	    Calendar cal2 = Calendar.getInstance();
	    SimpleDateFormat formatter1 = new SimpleDateFormat("yyyy/MM/dd");
	    SimpleDateFormat formatter2 = new SimpleDateFormat("yyyy/MM/dd"); 
	Date date_old = null;
	try {
		date_old = formatter1.parse(date1);
	} catch (ParseException e) {
		// TODO Auto-generated catch block
		e.printStackTrace();
	}
	Date date_new = null;
	try {
		date_new = formatter2.parse(date2);
	} catch (ParseException e) {
		// TODO Auto-generated catch block
		e.printStackTrace();
	}
	long milis1 = date_old.getTime();
	long milis2 = date_new.getTime();

	long diff = (milis2 - milis1)/86400000;       
	return diff;
	 }
	
	/**
	 * This method Calculates statistics for the loans of popular books and regular
	 * @return the StatisticInfo
	 */
	public static StatisticInfo StatisticInfoReport() {

		Statement stmt;
		StatisticInfo res =  new StatisticInfo();
		ArrayList<Long> diff_date0=new ArrayList<Long>();
		ArrayList<Long> diff_date1=new ArrayList<Long>();
		try {	
	
		stmt = EchoServer.conn.createStatement();
		ResultSet rs = stmt.executeQuery("SELECT l.loanDate ,l.reallyReturnDate FROM books b,loanshistory l,copies c WHERE b.BookId=c.BookId && c.copyId=l.copyId && b.rank='0';");
		while (rs.next())
		{		
			diff_date0.add(diffDate(rs.getString(1),rs.getString(2)));
		}
		Collections.sort(diff_date0);
		long sum0=0;
		double avg0=0;
		long mid0=0;
		if (diff_date0.size()>0)
		{
			for (int i=0;i<diff_date0.size();i++)
				sum0=sum0+diff_date0.get(i);
			avg0=sum0/diff_date0.size();
			if(diff_date0.size()%2==1)
				mid0 = diff_date0.get((int) ((diff_date0.size()/2)));
			else
			{   			
				mid0 = (diff_date0.get((int) ((diff_date0.size()/2)-1))+diff_date0.get((int) ((diff_date0.size()/2))))/2;
			}
		}
		ResultSet rs1 = stmt.executeQuery("SELECT l.loanDate ,l.reallyReturnDate FROM books b,loanshistory l,copies c WHERE b.BookId=c.BookId && c.copyId=l.copyId && b.rank='1';");
		while (rs1.next())
		{
			diff_date1.add(diffDate(rs1.getString(1),rs1.getString(2)));
		}
		Collections.sort(diff_date1);
		double avg1=0;
		long mid1=0;
		if (diff_date1.size()>0)
		{
			long sum1=0;
			for (int i=0;i<diff_date1.size();i++)
				sum1=sum1+diff_date1.get(i);
			avg1=sum1/diff_date1.size();
			
			if(diff_date1.size()%2==1)
				mid1 = diff_date1.get((int) ((diff_date1.size()/2)));	
			else
				mid1 = (diff_date1.get((int) ((diff_date1.size()/2)-1))+diff_date1.get((int) ((diff_date1.size()/2))))/2;
		}
		res= new StatisticInfo(mid0,mid1,avg0,avg1);
		res.statisticList0=diff_date0;
		res.statisticList1=diff_date1;

		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}

		return res;
}
	
	/**
	 * @return instance of LateReport
	 * this method produce a late report*/
	public static LateReport lateReport() {
		// TODO Auto-generated method stub
		Statement stmt;
		LateReport res =  new LateReport();
		ArrayList<Long> diff_date1=new ArrayList<Long>();
		try {	
			stmt = EchoServer.conn.createStatement();
			ResultSet rs1 = stmt.executeQuery("SELECT l.returnDate ,l.reallyReturnDate FROM books b,loanshistory l,copies c WHERE b.BookId=c.BookId && c.copyId=l.copyId;");
			while (rs1.next())
			{
				Long diff = diffDate(rs1.getString(1),rs1.getString(2));
				if (diff>0)
					diff_date1.add(diff);
			}
			Collections.sort(diff_date1);
			double avg1=0;
			long mid1=0;
			if (diff_date1.size()>0)
			{
				long sum1=0;
				for (int i=0;i<diff_date1.size();i++)
					sum1=sum1+diff_date1.get(i);
				avg1=sum1/diff_date1.size();
				
				if(diff_date1.size()%2==1)
					mid1 = diff_date1.get((int) ((diff_date1.size()/2)));	
				else
					mid1 = (diff_date1.get((int) ((diff_date1.size()/2)-1))+diff_date1.get((int) ((diff_date1.size()/2))))/2;
			}
			res= new LateReport(mid1,avg1,diff_date1.size());
			res.lateReportList=diff_date1;
			
		} catch (SQLException e) {
			e.printStackTrace();
		}

		return res;
	}
	
	/**
	 * @return instance of LateReport
	 * this method produce a late report for a given book*/
	public static LateReport lateReportBook(String bid) {
		// TODO Auto-generated method stub
		Statement stmt;
		LateReport res =  new LateReport();
		ArrayList<Long> diff_date1=new ArrayList<Long>();
		try {	
			stmt = EchoServer.conn.createStatement();
			System.out.println("SELECT l.returnDate ,l.reallyReturnDate FROM loanshistory l,copies c WHERE c.BookId='"+bid+"' && c.copyId=l.copyId;");
			ResultSet rs1 = stmt.executeQuery("SELECT l.returnDate ,l.reallyReturnDate FROM loanshistory l,copies c WHERE c.BookId='"+bid+"' && c.copyId=l.copyId;");
			while (rs1.next())
			{
				Long diff = diffDate(rs1.getString(1),rs1.getString(2));
				if (diff>0)
					diff_date1.add(diff);
			}
			Collections.sort(diff_date1);
			double avg1=0;
			long mid1=0;
			if (diff_date1.size()>0)
			{
				long sum1=0;
				for (int i=0;i<diff_date1.size();i++)
					sum1=sum1+diff_date1.get(i);
				avg1=sum1/diff_date1.size();
				
				if(diff_date1.size()%2==1)
					mid1 = diff_date1.get((int) ((diff_date1.size()/2)));	
				else
					mid1 = (diff_date1.get((int) ((diff_date1.size()/2)-1))+diff_date1.get((int) ((diff_date1.size()/2))))/2;
			}
			res= new LateReport(mid1,avg1,diff_date1.size());
			res.lateReportList=diff_date1;
			
		} catch (SQLException e) {
			e.printStackTrace();
		}

		return res;
	}
	
	 /**
	  * @return true if disconnecting all users succeeded, else false
	  * this method change every user's status to 0(disconnected)*/
	 public static boolean disconnectAll()
	 {
	  Statement stmt;
	  try 
	   {
	    stmt = EchoServer.conn.createStatement();
	    ResultSet rs = stmt.executeQuery("SELECT * FROM user;");
	     while(rs.next())
	     {
	      if(rs.getInt(3)==1)
	    	  changeStatus(rs.getString(1),0);
	     }
	   }
	  catch (SQLException e) {e.printStackTrace();}
	  return false;
	 }
}

